/*
*************************************************************************
*西瑞克斯研发管理系统
*版 本: 1.0   
*日 期：02/16/2012    
*作 者： 王浩 QQ：873987060   
*************************************************************************   
*/ 
package com.application.systemmanager.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.application.systemmanager.commons.Constants;
import com.application.systemmanager.entity.AllowancePut;
import com.application.systemmanager.entity.Notice;
import com.application.systemmanager.entity.ProcessView;
import com.application.systemmanager.entity.SystemConfig;
import com.application.systemmanager.struts.form.AllowanceForm;
import com.application.systemmanager.struts.form.NoticeForm;
import com.application.systemmanager.struts.form.ProcessForm;
import com.application.systemmanager.struts.form.SystemHelpForm;
import com.application.workbench.struts.form.WorkBenchForm;
import com.manager.BaseDao;
import com.manager.commons.CommonUtil;
import com.manager.commons.page.PageBean;

/**
 * Copyrights 1997-2008 西安协同时光软件有限公司<br />
 * Description:NoticeDAO<br />
 * Version:V0.1<br />
 * Author: Code Generator <br />
 * History: <br />
 * 03/08/2010 SyncBase Code Generator initial version.
 */
public class SystemManagerDAO extends BaseDao {
	private static final Logger logger = Logger.getLogger(SystemManagerDAO.class);

	private Notice notice;
	private JdbcTemplate jdbcTemplate;
	public Notice getNotice() {
		return this.notice;
	}

	public void setNotice(Notice notice) {
		this.notice = notice;
	}

	public Integer getAllCountNotice(NoticeForm noticeForm) {
		// 储存查询条件的buffer
		StringBuffer buf = new StringBuffer();
		List args = new ArrayList();
		buf.append("select count(*) from Notice t where  1=1");
		// 查询字符串
		String strSql = getStringBuffer(noticeForm, buf,args).toString();
		Integer count = Integer.parseInt(this.getHibernateTemplate().find(strSql,args.toArray()).get(0).toString());
		
		return count;
	}
	
	public Integer getAllCountHelp(SystemHelpForm helpForm) {
		// 储存查询条件的buffer
		StringBuffer buf = new StringBuffer();
		List args = new ArrayList();
		buf.append("select count(*) from SystemHelp t where  1=1");
		// 查询字符串
		String strSql = getStringBufferHelp(helpForm, buf,args).toString();
		Integer count = Integer.parseInt(this.getHibernateTemplate().find(strSql,args.toArray()).get(0).toString());
		
		return count;
	}
	
	
	public Integer getAllCountAllowance(AllowanceForm allowanceForm) {
		// 储存查询条件的buffer
		StringBuffer buf = new StringBuffer();
		buf.append("select count(*) from AllowancePut t where  1=1");
		
		Integer count = Integer.parseInt(this.getHibernateTemplate().find(buf.toString()).get(0).toString());
		
		return count;
	}
	
    public void deleteNotices(NoticeForm noticeForm){    	
    	String hql = " delete from Notice where id in  ( " + noticeForm.getIds() + ")";
		Session session = this.getSession();
		try
		{
			session.createQuery(hql).executeUpdate();
		}
		catch(RuntimeException re)
		{
		    logger.error("deleteNotices failed");	
			throw re;
		}
		finally
		{
			if (session != null)
			{
				releaseSession(session);
			}
		}
    }
    public void deleteSystemHelp(SystemHelpForm systemHelpForm){    	
    	String hql = " delete from SystemHelp where id in  ( " + systemHelpForm.getIds() + ")";
		Session session = this.getSession();
		try
		{
			session.createQuery(hql).executeUpdate();
		}
		catch(RuntimeException re)
		{
		    logger.error("deleteNotices failed");	
			throw re;
		}
		finally
		{
			if (session != null)
			{
				releaseSession(session);
			}
		}
    }
   
    
	public List searchNotice(NoticeForm noticeForm, final PageBean pageBean) {
		StringBuffer buf = new StringBuffer();

		// 返回list集合
		List list = null;
		final List args = new ArrayList();

		buf.append("from Notice t  where 1=1 ");

		// 查询字符串条件
		buf = getStringBuffer(noticeForm, buf,args);

		// 传递查询条件buf值
		pageBean.setStringBuffer(buf);

		// 分页取得字符串集合
		list = getHibernateTemplate().executeFind(new HibernateCallback() {

			public Object doInHibernate(Session session) throws HibernateException, SQLException {

				Query query = session.createQuery(pageBean.getStringBuffer().toString() + " order by id Desc ");

				// 设置开始页
				query.setFirstResult(pageBean.getPageSize() * (pageBean.getPageIndex() - 1));

				// 设置结束页
				query.setMaxResults(pageBean.getPageSize());
                setParameter(query, args);
				List list = query.list();

				return list;
			}
		});
		return list;
	}
	public List searcHelp(SystemHelpForm helpForm, final PageBean pageBean) {
		StringBuffer buf = new StringBuffer();

		// 返回list集合
		List list = null;
		final List args = new ArrayList();

		buf.append("from SystemHelp t  where 1=1 ");

		// 查询字符串条件
		buf = getStringBufferHelp(helpForm, buf,args);

		// 传递查询条件buf值
		pageBean.setStringBuffer(buf);

		// 分页取得字符串集合
		list = getHibernateTemplate().executeFind(new HibernateCallback() {

			public Object doInHibernate(Session session) throws HibernateException, SQLException {

				Query query = session.createQuery(pageBean.getStringBuffer().toString() + " order by id Desc ");

				// 设置开始页
				query.setFirstResult(pageBean.getPageSize() * (pageBean.getPageIndex() - 1));

				// 设置结束页
				query.setMaxResults(pageBean.getPageSize());
                setParameter(query, args);
				List list = query.list();

				return list;
			}
		});
		return list;
	}
	public void deleteEarlyWaring(String bussinessID,String type,String year,String month,String quarter){
//		String month = CommonUtil.getCurrentMonth();
//		String year = CommonUtil.getCurrentYear();
//		String quarter = CommonUtil.getCurrentQuarter();
		String hql = " delete from Earlywarning where bussinessid='"+bussinessID+"' and type='"+type+"' and bak1='"+year+"' and (bak2='"+month+"' or bak3='"+quarter+"')";
		Session session = this.getSession();
		 
		try
		{
			session.createQuery(hql).executeUpdate();
		}
		catch(RuntimeException re)
		{
		    logger.error("deleteEarlyWaring failed");	
			throw re;
		}
		finally
		{
			if (session != null)
			{
				releaseSession(session);
			}
		}
	}
	
	public List searchAllowance(AllowanceForm allowanceForm) {
		StringBuffer buf = new StringBuffer();

		List allowanceList=new ArrayList();
		AllowancePut allowance=null;
		String sql="select a.id,a.money,b.listName projectType,c.listName personLevel from AllowancePut a left join basedatalist b on a.projectType = b.listcode left join basedatalist c on a.personLevel = c.listcode";
		List list =getListForPage(sql);
		for(int i=0;i<list.size();i++){
			allowance=new AllowancePut();
			Map map=(Map)list.get(i);
			allowance.setId(map.get("id").toString());
			allowance.setProjectType(map.get("projecttype").toString());
			allowance.setMoney(map.get("money").toString());
			allowance.setPersonLevel(map.get("personlevel").toString());
			
			allowanceList.add(allowance);
		}
		return allowanceList;
	}
	
	
	public List searchPlanReportWarningList() {
		StringBuffer buf = new StringBuffer();
		List earlyWarningList=new ArrayList();
		buf.append(" SELECT ID BUSSINESSID,56001 TYPE,YEAR(GETDATE()) YEA,MONTH(GETDATE()) MON,(SELECT DATEPART(QUARTER,GETDATE())) QUARTER FROM A_PROJECTWORKSHEETS A WHERE ID NOT IN (select workSheetsID from a_progress where year(writeTime) = year(getdate())  " );
		buf.append(" and ( month(writeTime) = month(getdate()) or writeQuarter = datepart(quarter,GETDATE())) AND workSheetsID IS NOT NULL " );
		buf.append(" ) AND STATE <> '42001' ");
		int currentMonth = Integer.parseInt(CommonUtil.getCurrentMonth());
        if(currentMonth%3!=0){
    	   buf.append(" AND PROJECTCATEGORY='24002'" );
        } 
		buf.append(" AND ID NOT IN( " );
		buf.append("   select workSheetsID from a_acceptance where state='42008'" );
		buf.append(" ) and getdate()<=("+getEndTimeSql()+")");
		List list =getListForPage(buf.toString());
		for(int i=0;i<list.size();i++){
			WorkBenchForm workBenchFormtemp = new WorkBenchForm();
			Map map=(Map)list.get(i);
 			workBenchFormtemp.setBussinessID(map.get("BUSSINESSID")==null?"":map.get("BUSSINESSID").toString());
 			String year = map.get("YEA")==null?"":map.get("YEA").toString();
 			String month = map.get("MON")==null?"":map.get("MON").toString();
 			String quarter = map.get("QUARTER")==null?"":map.get("QUARTER").toString();
 			workBenchFormtemp.setBussinessName(year+","+month+","+quarter);
 			workBenchFormtemp.setType(map.get("TYPE")==null?0:Long.valueOf(map.get("TYPE").toString()));
 			earlyWarningList.add(workBenchFormtemp);
		}
		return earlyWarningList;
	}
	
	   /**
	    * 
	   * 拼装项目结束时间的sql语句  
	   *  
	   * @return
	    */
	   private String getEndTimeSql(){
		   StringBuffer sql = new StringBuffer();
		   sql.append(" SELECT ENDTIME FROM ( ");
		   sql.append("	   select dateadd(dd,-1,DATEADD(mm,DATEDIFF(m,0,endtime)+1,0)) endtime,worksheetsid from ( ");
		   sql.append("	   select  max(inputTime) inputTime,min(startTime) startTime,");
		   sql.append("   case when max(endTime) is null then max(startTime) else max(endTime) end endTime ,max(quarter) quarter,worksheetsid from a_plan");   
		   sql.append("	   group by worksheetsid,inputTime");
		   sql.append("	   union ");
		   sql.append("	   select inputtime,starttime,endtime,1,id from A_PROJECTWORKSHEETS  ");
		   sql.append("	   ) a where a.worksheetsid  not in (select worksheetsid from a_plan)  or  a.inputTime = (select max(inputtime) from (select  max(inputTime) inputTime,min(startTime) startTime,worksheetsid,");
		   sql.append("   case when max(endTime) is null then max(startTime) else max(endTime) end endTime ,max(quarter) quarter from a_plan where worksheetsid = a.worksheetsid group by inputTime,worksheetsid");
		   sql.append("   ) b group by b.worksheetsid ) ");
		   sql.append("	   ) V WHERE V.WORKSHEETSID=A.ID");
		   return sql.toString();
	   }
	
	public AllowanceForm getAllowance(String id) {
		StringBuffer buf = new StringBuffer();
		AllowanceForm allowance=new AllowanceForm();;
		String sql="select a.id,a.money,a.projectType,a.personLevel,b.listName projectTypeText,c.listName personLevelText from AllowancePut a left join basedatalist b on a.projectType = b.listCode left join basedatalist c on a.personLevel = c.listCode where a.id="+id+"";
		List list =getListForPage(sql);
		for(int i=0;i<list.size();i++){
			
			Map map=(Map)list.get(i);
			allowance.setId(Integer.parseInt(map.get("id").toString()));
			
			allowance.setProjectType(map.get("projecttype").toString());
			allowance.setProjectTypeText(map.get("projecttypetext").toString());
			allowance.setMoney(map.get("money").toString());
			allowance.setPersonLevel(map.get("personlevel").toString());
			allowance.setPersonLevelText(map.get("personleveltext").toString());
			
		
		}
		return allowance;
	}
	

	/**
	 * 查询条件的封装
	 * 
	 * @param departmentForm
	 * @return
	 */
	private StringBuffer getStringBuffer(NoticeForm noticeForm, StringBuffer buf,List args) {

		/*
		 * 需要加入查询条件时封装
		 */
		if(StringUtils.isNotBlank(noticeForm.getNoticeTitle())){
			buf.append(" and t.noticeTitle like ? ");
			args.add("%" + noticeForm.getNoticeTitle().trim() + "%");
		}
		if(null != noticeForm.getNoticeType() && noticeForm.getNoticeType().longValue() != 0){
			buf.append(" and t.noticeType = " + noticeForm.getNoticeType());
		}
		if(StringUtils.isNotBlank(noticeForm.getStartDate())){
			buf.append(" and t.noticeDate>='" + noticeForm.getStartDate() + "'");
		}
		if(StringUtils.isNotBlank(noticeForm.getEndDate())){
			buf.append(" and t.noticeDate<='" + noticeForm.getEndDate() + "'");
		}
		return buf;
	}
	/**
	 * 查询条件的封装
	 * 
	 * @param departmentForm
	 * @return
	 */
	private StringBuffer getStringBufferHelp(SystemHelpForm helpForm, StringBuffer buf,List args) {

		/*
		 * 需要加入查询条件时封装
		 */
		if(StringUtils.isNotBlank(helpForm.getHelpTitle())){
			buf.append(" and t.helpTitle like ? ");
			args.add("%" + helpForm.getHelpTitle().trim() + "%");
		}
		
		return buf;
	}

    /**
     * 
    * 分配置类型 得到配置Map    key: type值  value:相应的配置集合
    *  
    * @return
     */
	public Map searchSystemConfig() {
		Map configMap = new HashMap();
		final StringBuffer buf = new StringBuffer();
		// 返回list集合
		List list = null;

		buf.append("from SystemConfig t  where 1=1 ");
		list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery(buf.toString() + " order by t.configType ");
				List list = query.list();
				return list;
			}
		});
		if(list != null){
			for(Iterator iterator = list.iterator(); iterator.hasNext();) {
				SystemConfig config = (SystemConfig) iterator.next();
				if(configMap.containsKey(config.getConfigType())){
					List listTemp = (List) configMap.get(config.getConfigType());
					listTemp.add(config);
				}else{
					List listTwo = new ArrayList();
					listTwo.add(config);
					configMap.put(config.getConfigType(), listTwo);
				}
			}
		}
		
		return configMap;
	}

	/**
	 * 
	* TODO 根据配置CODE 得到配置信息
	*  
	* @param code
	* @return
	 */
	public SystemConfig findSystemConfigByCode(String code){
		 StringBuffer sql = new StringBuffer();
		 sql.append(" from SystemConfig t  where t.cofigCode = " + code);
		 SystemConfig systemConfig = (SystemConfig) getHibernateTemplate().find(sql.toString()).get(0);
		 return systemConfig;
	}
	
	/**
	 * 
	* TODO 根据用户名判断密码是否正确
	*  
	* @param code
	* @return
	 */
	public boolean checkUserPassword(String username,String password){
		StringBuffer buf = new StringBuffer();
		AllowanceForm allowance=new AllowanceForm();;
		String sql="select * from t_usertable where username='"+username+"' and userpassword='"+password+"'";
		List list =getListForPage(sql);
		if(list.size()>0){
			return true;
		}else{
			return false;
		}
	}
	//更新用户密码
	public void updateUserPassword(String username ,String password){
		Session session = this.getSession();
		String sql="update t_usertable set userpassword='"+password+"' where username ='"+username+"'";
		try {
			session.createSQLQuery(sql).executeUpdate();
		} catch (RuntimeException re) {
			logger.error("searchAcceptance failed");
			throw re;
		} finally {
			if (session != null) {
				releaseSession(session);
			}
		}

	}
	
	/**
	 * 工作台中的通知公告列表
	 * 
	 * @return
	 */
	public List findWorkBenchNoticeList() {
		StringBuffer buf = new StringBuffer();
		String sql="select * from Notice  order by id desc limit " + Constants.FETCH_SIZE ;
		List noticeList = new ArrayList();
		List list =getListForPage(sql);
		for(int i=0;i<list.size();i++){
			Notice notice=new Notice();
			Map map=(Map)list.get(i);
			notice.setId(Long.parseLong(map.get("id").toString()));
			notice.setNoticeTitle(map.get("notice_title").toString());
			noticeList.add(notice);
			
		
		}
		return noticeList;
	}
	
	public Integer findProcessCount(ProcessForm processForm){
		Session session = this.getSession();
        StringBuffer buf = new StringBuffer();
    	List processList = new ArrayList();
		buf.append(" select count(0) from t_ff_rt_processinstance a ");
		buf.append("left join usertable b on a.creator_id = b.id ");
		buf.append("LEFT JOIN ");
		buf.append("( ");
		buf.append("  select wi.actor_id,ta.processinstance_Id from t_ff_rt_workitem wi left join t_ff_rt_taskinstance ta   ");
		buf.append("   on wi.taskinstance_id = ta.id left join yf_buss_taskinstance yt  ");
		buf.append(" on ta.id=yt.taskinstance_id  where  (wi.state='0' or wi.state='1') group by ta.PROCESSINSTANCE_ID ");
		buf.append(")  c  on  a.id = c.processinstance_Id ");
		buf.append("	left join usertable d on c.actor_id = d.id ");
		buf.append("	left join ");
		buf.append("( ");
		buf.append("  select t.PROCESSINSTANCE_ID,u.BUSS_TYPE from t_ff_rt_taskinstance t left JOIN yf_buss_taskinstance u ");
		buf.append(" on t.id = u.TASKINSTANCE_ID  group by t.PROCESSINSTANCE_ID ");
		buf.append("	) e  on a.id = e.PROCESSINSTANCE_ID where 1=1");
        if(StringUtils.isNotBlank(processForm.getOrg())){
        	buf.append(" and b.org = '" + processForm.getOrg() +"' ");
        }
        if(processForm.getBussType()!=null && processForm.getBussType().longValue()!=0){
        	buf.append(" and e.buss_type=" + processForm.getBussType());
        }
        if(StringUtils.isNotBlank(processForm.getCreatename())){
        	buf.append(" and b.rname = '" + processForm.getCreatename() + "'");
        }
        if(StringUtils.isNotBlank(processForm.getStartDateStr())){
        	buf.append(" and a.CREATED_TIME >= '" + processForm.getStartDateStr() + "'");
        }
        if(StringUtils.isNotBlank(processForm.getEndDateStr())){
        	buf.append(" and a.CREATED_TIME <= '" + processForm.getEndDateStr() + "'");
        }
        return this.getJdbcTemplate().queryForInt(buf.toString(),null);
	}
	
	public List findProcessList(ProcessForm processForm,PageBean pageBean){
		Session session = this.getSession();
        StringBuffer buf = new StringBuffer();
    	List processList = new ArrayList();
		buf.append(" select a.id procId,e.BUSS_TYPE bussType,b.rname createname,a.CREATED_TIME createTime,case a.STATE when 7 then '结束' else '运行中' end  state,b.org,d.rname agent from t_ff_rt_processinstance a ");
		buf.append("left join usertable b on a.creator_id = b.id ");
		buf.append("LEFT JOIN ");
		buf.append("( ");
		buf.append("  select wi.actor_id,ta.processinstance_Id from t_ff_rt_workitem wi left join t_ff_rt_taskinstance ta   ");
		buf.append("   on wi.taskinstance_id = ta.id left join yf_buss_taskinstance yt  ");
		buf.append(" on ta.id=yt.taskinstance_id  where  (wi.state='0' or wi.state='1') group by ta.PROCESSINSTANCE_ID ");
		buf.append(")  c  on  a.id = c.processinstance_Id ");
		buf.append("	left join usertable d on c.actor_id = d.id ");
		buf.append("	left join ");
		buf.append("( ");
		buf.append("  select t.PROCESSINSTANCE_ID,u.BUSS_TYPE from t_ff_rt_taskinstance t left JOIN yf_buss_taskinstance u ");
		buf.append(" on t.id = u.TASKINSTANCE_ID  group by t.PROCESSINSTANCE_ID ");
		buf.append("	) e  on a.id = e.PROCESSINSTANCE_ID where 1=1");
		   if(StringUtils.isNotBlank(processForm.getOrg())){
	        	buf.append("  and b.org = '" + processForm.getOrg() +"' ");
	        }
		   if(processForm.getBussType()!=null && processForm.getBussType().longValue()!=0){
	        	buf.append(" and e.buss_type=" + processForm.getBussType());
	        }
	        if(StringUtils.isNotBlank(processForm.getCreatename())){
	        	buf.append(" and b.rname = '" + processForm.getCreatename() + "'");
	        }
	        if(StringUtils.isNotBlank(processForm.getStartDateStr())){
	        	buf.append(" and a.CREATED_TIME >= '" + processForm.getStartDateStr() + "'");
	        }
	        if(StringUtils.isNotBlank(processForm.getEndDateStr())){
	        	buf.append(" and a.CREATED_TIME <= '" + processForm.getEndDateStr() + "'");
	        }
        buf.append(" order by a.created_time desc ");
		try {
			Query query = session.createSQLQuery(buf.toString()).setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			if(pageBean != null) {
				// 设置开始页
				query.setFirstResult(pageBean.getPageSize() * (pageBean.getPageIndex() - 1));
				// 设置结束页
				query.setMaxResults(pageBean.getPageSize());
			}
			List<Map> listMap = query.list();
			
			
		
	
		 
		for(int i=0;i<listMap.size();i++){
			ProcessView temp = new ProcessView();
			Map map=(Map)listMap.get(i);
			temp.setProcId(getString(map.get("procId"))); 
			temp.setBussType(map.get("bussType")==null?"":map.get("bussType").toString());
			temp.setCreatename(getString(map.get("createname")));
			temp.setCreateTime(getDate(map.get("createTime")));
			temp.setState(getString(map.get("state")));
			temp.setOrg(getString(map.get("org")));
			temp.setAgent(getString(map.get("agent")));
			processList.add(temp);
		}
		}
		catch(Exception re) {
			System.out.println(re);
			logger.error("getPageInfo failed");
			return null;
		}
		finally {
			if(session != null) {
				releaseSession(session);
			}
		}
		return processList;
	}
	
	
	
	private String getString(Object o){
		if(o!=null){
			return (String)o;
		}
		return "";
	}
	private Date getDate(Object o){
		if(o!=null){
			return (Date)o;
		}
		return null;
	}

}
